package cn.com.elite;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

public class ISBean {
	private static final Log log=LogFactory.getLog(ISBean.class);
	private DataSource dataSource;
    private DataSourceTransactionManager tran;
	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public DataSourceTransactionManager getTran() {
		return tran;
	}
	public void setTran(DataSourceTransactionManager tran) {
		this.tran = tran;
	}
	
    public String chaxunString(){
    	String result="100";
    	String sql="select PARAMETERVALUE from SYSC_PARAMETER where PARAMETER_id='TBTIME'";
    	try{
        	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        	result=(String)jdbcTemplate.queryForObject(sql,java.lang.String.class);
    	}catch(Exception e){
    		log.error("chaxunString(String sql):"+Util.getERRO(e));
    	}
    	log.debug("result_dian:"+result);
        return result;
    }
    public String chaxunString1(){
    	String result="100";
    	String sql="select PARAMETERVALUE from SYSC_PARAMETER where PARAMETER_id='TBECTM'";
    	try{
        	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        	result=(String)jdbcTemplate.queryForObject(sql,java.lang.String.class);
    	}catch(Exception e){
    		log.error("chaxunString(String sql):"+Util.getERRO(e));
    	}
    	log.debug("result_dian:"+result);
        return result;
    }
    public String chaxun_string(String sql,DataSource dataSource1){
    	String result="";
    	try{
        	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource1);
        	result=(String)jdbcTemplate.queryForObject(sql,java.lang.String.class);
    	}catch(Exception e){
    		log.error("chaxun_string(String sql,DataSource dataSource1):"+Util.getERRO(e));
    	}
    	log.debug("result_dian:"+result);
        return result;
    }
	public List chaxun_sql1(String sql){
		List result=null;
		try{		
			JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
			result=jdbcTemplate.queryForList(sql);
		}catch(Exception e){
			log.error("chaxun_sql_ERRO:"+Util.getERRO(e));
		}
		return result;
	}
	
	public List chaxun_sql(String sql,DataSource dataSource1){
		List result=null;
		try{	
			log.debug("sql:"+sql);
			JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource1);
			result=jdbcTemplate.queryForList(sql);
		}catch(Exception e){
			log.error("chaxun_sql_ERRO:"+Util.getERRO(e));
		}
		return result;
	}
	
	public int update_sql(String sql,DataSource dataSource1){
		int result=0;
		try{
			JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource1);
			result=jdbcTemplate.update(sql);
		}catch(Exception e){
			log.error("update_sql_ERRO:"+Util.getERRO(e));
		}
		return result;
	}
	
    public String ISIvrPay(String ORDER_NUMBER,String RESPONSE_CODE,String RESPONSE_DESC,String BANK_CODE,String ACCOUNT_TYPE,String PAYMENT_ID,String TRANS_DATE,String TRANS_TIME,String ERR_MSG,String ERR_CODE,String BankAccount,int flag,DataSource dataSource1,String PAYMENT_ID1,String RealOrderNo){
    	log.info("---------ISIvrPay(68)------------");
    	final String a=ORDER_NUMBER;
    	final String b=RESPONSE_CODE;
    	final String c=RESPONSE_DESC;
    	final String d=BANK_CODE;
    	final String e=ACCOUNT_TYPE;
    	final String f=PAYMENT_ID;
    	final String g=TRANS_DATE;
    	final String h=TRANS_TIME;
    	final String i=ERR_MSG;
    	final String j=ERR_CODE;
    	final String k=BankAccount;
    	final int l=flag;
    	final String m=PAYMENT_ID1;
    	final String o=RealOrderNo;
    	log.info("ORDER_NUMBER:"+a);
    	log.info("RESPONSE_CODE:"+b);
    	log.info("RESPONSE_DESC:"+c);
    	log.info("BANK_CODE:"+d);
    	log.info("ACCOUNT_TYPE:"+e);
    	log.info("PAYMENT_ID:"+f);
    	log.info("TRANS_DATE:"+g);
    	log.info("TRANS_TIME:"+h);
    	log.info("ERR_MSG:"+i);
    	log.info("ERR_CODE:"+j);
    	log.info("BankAccount:"+k);
    	log.info("flag:"+l);
    	log.info("PAYMENT_ID1:"+m);
    	log.info("RealOrderNo:"+o);
    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource1);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator(){
                public CallableStatement createCallableStatement(Connection con){
                    String storedProc="{call ISIvrPay(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs=null;
                    try{
                    	cs=con.prepareCall(storedProc);
                   }catch(Exception e){
                	   log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                	   cs.setString(1, a);// 设置输入参数的值
                   	   cs.setString(2, b);// 设置输入参数的值
                   	   cs.setString(3, c);// 设置输入参数的值
                   	   cs.setString(4, d);// 设置输入参数的值
                   	   cs.setString(5, e);// 设置输入参数的值
                   	   cs.setString(6, f);// 设置输入参数的值
                   	   cs.setString(7, g);// 设置输入参数的值
                   	   cs.setString(8, h);// 设置输入参数的值	
                   	   cs.setString(9, i);// 设置输入参数的值
                   	   cs.setString(10, j);// 设置输入参数的值
                   	   cs.setString(11, k);// 设置输入参数的值
                   	   cs.setInt(12, l);// 设置输入参数的值
                   	   cs.setInt(13, 0);// 设置输入参数的值
                   	   cs.setString(14, m);// 设置输入参数的值
                   	   cs.setString(15, o);// 设置输入参数的值
                   }catch(Exception e){
                	   log.info("设置输入参数的值错误:"+Util.getERRO(e));
                   }
                   try{
                	   cs.registerOutParameter(13, Types.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){     	   
                	   log.info("注册输出参数的类型错误:"+Util.getERRO(e));
                   }
                    return cs;
                }
            }, new CallableStatementCallback(){
                public Object doInCallableStatement(CallableStatement cs){
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+Util.getERRO(e));
                    }
                    try{                   	
                    	return cs.getInt(13);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(13):"+Util.getERRO(e));
                    	return null;
                    }
            }
        }).toString();
        log.info("ISIvrPay_param2Value:"+param2Value);
        return param2Value;
    }

    
    public String ISIvrPay_log(String OrderNo,String STEP_DESC,String Param,String log_type,int flag,String realtionid,DataSource dataSource1){
    	log.info("---------ISIvrPay_log(155)------------");
    	final String a=OrderNo;
    	final String b=STEP_DESC;
    	final String c=Param;
    	final String d=log_type;
    	final int e=flag;
    	final String f=realtionid;
    	log.info("OrderNo:"+a);
    	log.info("STEP_DESC:"+b);
    	log.info("Param:"+c);
    	log.info("log_type:"+d);
    	log.info("flag:"+e);
    	log.info("realtionid:"+f);
    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource1);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator(){
                public CallableStatement createCallableStatement(Connection con){
                    String storedProc="{call ISIvrPay_log(?,?,?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs=null;
                    try{
                    	cs=con.prepareCall(storedProc);             
                    }catch(Exception e){
                    	log.info("cs = con.prepareCall(storedProc)错误:"+e);
                    }
                   try{               	   
                	   cs.setString(1,a);// 设置输入参数的值
                   	   cs.setString(2,b);// 设置输入参数的值
                   	   cs.setString(3,c);// 设置输入参数的值
                       cs.setInt(4,0);// 设置输入参数的值
                       cs.setString(5,d);// 设置输入参数的值
                       cs.setInt(6,e);// 设置输入参数的值
                       cs.setString(7,f);// 设置输入参数的值
                   }catch(Exception e){
                	   log.info("设置输入参数的值错误:"+Util.getERRO(e));
                   }
                   try{
                	   cs.registerOutParameter(4,Types.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){
                	   log.info("注册输出参数的类型错误:"+Util.getERRO(e));
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+Util.getERRO(e));
                    }
                    try{
                    	   return cs.getInt(4);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(4):"+Util.getERRO(e));
                    	return null;
                    }
            }
        }).toString();
        log.info("ISIvrPay_log_param2Value:"+param2Value);
        return param2Value;
    }

    public String ISIvrPay_Tongbu(String realtionID,int flag,DataSource dataSource1,String ERR_CODE,String ERR_DESC) {
    	log.info("---------ISIvrPay_log(217)------------");
    	final String a=realtionID;
    	final int b=flag;
    	final String c=ERR_CODE;
    	final String d=ERR_DESC;
    	log.info("appl_no_qa:"+realtionID);
    	log.info("flag:"+flag);
    	log.info("ERR_CODE:"+ERR_CODE);
    	log.info("ERR_DESC:"+ERR_DESC);
    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource1);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){            
                    String storedProc="{call ISIvrPay_Tongbu(?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs=null;
                    try{
                    	cs=con.prepareCall(storedProc);
                   }catch(Exception e){
                	   log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                	   cs.setString(1,a);// 设置输入参数的值
                   	   cs.setInt(2,b);// 设置输入参数的值
                   	   cs.setInt(3,0);// 设置输入参数的值
                   	   cs.setString(4,c);// 设置输入参数的值
                   	   cs.setString(5,d);// 设置输入参数的值
                   }catch(Exception e){
                	   log.info("设置输入参数的值错误:"+Util.getERRO(e));
                   }
                   try{
                	   cs.registerOutParameter(3,Types.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){                	   
                	   log.info("注册输出参数的类型错误:"+Util.getERRO(e));
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+Util.getERRO(e));
                    }
                    try{
                    	   return cs.getInt(3);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(3):"+Util.getERRO(e));
                    	return null;
                    }
            }
        }).toString();
        log.info("ISIvrPay_ISIvrPay_Tongbu_param2Value:"+param2Value);
        return param2Value;
    }
    
    public String sp_appl_tb_insertlist(String guid){
    	final String v_appl_tb_list=guid;
    	log.info("v_appl_tb_list:"+v_appl_tb_list);
    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){            
                    String storedProc="{call sp_appl_tb_insertlist(?,?)}";// 调用的sql
                    CallableStatement cs=null;
                    try{
                    	cs=con.prepareCall(storedProc);
                   }catch(Exception e){
                	   log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                	   cs.setString(1, v_appl_tb_list);// 设置输入参数的值
                   	   cs.setInt(2,0);// 设置输入参数的值
                   }catch(Exception e){
                	   log.info("设置输入参数的值错误:"+Util.getERRO(e));
                   }
                   try{
                	   cs.registerOutParameter(2,Types.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){                	   
                	   log.info("注册输出参数的类型错误:"+Util.getERRO(e));
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+Util.getERRO(e));
                    }
                    try{
                    	   return cs.getInt(2);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(2):"+Util.getERRO(e));
                    	return null;
                    }
            }
        }).toString();
        log.info("v_appl_tb_list_param2Value:"+param2Value);
        return param2Value;
    }
    
    public String sp_appl_tb_return(String guid,int result_code){
    	final String list=guid;
    	log.info("list:"+list);
    	final int code=result_code;
    	log.info("code:"+code);
    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){            
                    String storedProc="{call sp_appl_tb_return(?,?,?)}";// 调用的sql
                    CallableStatement cs=null;
                    try{
                    	cs=con.prepareCall(storedProc);
                   }catch(Exception e){
                	   log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                	   cs.setString(1, list);// 设置输入参数的值
                   	   cs.setInt(2,code);// 设置输入参数的值
                       cs.setInt(3,0);// 设置输入参数的值
                   }catch(Exception e){
                	   log.info("设置输入参数的值错误:"+Util.getERRO(e));
                   }
                   try{
                	   cs.registerOutParameter(3,Types.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){                	   
                	   log.info("注册输出参数的类型错误:"+Util.getERRO(e));
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+Util.getERRO(e));
                    }
                    try{
                    	   return cs.getInt(3);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(3):"+Util.getERRO(e));
                    	return null;
                    }
            }
        }).toString();
        log.info("sp_appl_tb_return_param2Value:"+param2Value);
        return param2Value;
    }
    
    
    public String sp_appl_tb_updatetabc(String v_relation_id1,String v_appl_tb_list1){
    	final String v_relation_id=v_relation_id1;
    	log.info("v_relation_id:"+v_relation_id1);
    	final String v_appl_tb_list= v_appl_tb_list1;
    	log.info(" v_appl_tb_list:"+ v_appl_tb_list);
    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){            
                    String storedProc="{call sp_appl_tb_updatetabc(?,?,?)}";// 调用的sql
                    CallableStatement cs=null;
                    try{
                    	cs=con.prepareCall(storedProc);
                   }catch(Exception e){
                	   log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                	   cs.setString(1, v_relation_id);// 设置输入参数的值
                   	   cs.setString(2,v_appl_tb_list);// 设置输入参数的值
                       cs.setInt(3,0);// 设置输入参数的值
                   }catch(Exception e){
                	   log.info("设置输入参数的值错误:"+Util.getERRO(e));
                   }
                   try{
                	   cs.registerOutParameter(3,Types.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){                	   
                	   log.info("注册输出参数的类型错误:"+Util.getERRO(e));
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+Util.getERRO(e));
                    }
                    try{
                    	   return cs.getInt(3);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(3):"+Util.getERRO(e));
                    	return null;
                    }
            }
        }).toString();
        log.info("sp_appl_tb_updatetabc_param2Value:"+param2Value);
        return param2Value;
    }
    
    public String sp_appl_tb_query_return(String List1,int MEG_CODE1,String MEG_DESC1){
    	final String List=List1;
    	log.info("List:"+List);
    	final int MEG_CODE=MEG_CODE1;
    	log.info(" MEG_CODE:"+MEG_CODE);
    	final String MEG_DESC=MEG_DESC1;
    	log.info("MEG_DESC:"+MEG_DESC);
    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){            
                    String storedProc="{call sp_appl_tb_query_return(?,?,?,?)}";// 调用的sql
                    CallableStatement cs=null;
                    try{
                    	cs=con.prepareCall(storedProc);
                   }catch(Exception e){
                	   log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                	   cs.setString(1, List);// 设置输入参数的值
                   	   cs.setInt(2,MEG_CODE);// 设置输入参数的值
                       cs.setString(3,MEG_DESC);// 设置输入参数的值
                       cs.setInt(4,0);// 设置输入参数的值
                   }catch(Exception e){
                	   log.info("设置输入参数的值错误:"+Util.getERRO(e));
                   }
                   try{
                	   cs.registerOutParameter(4,Types.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){                	   
                	   log.info("注册输出参数的类型错误:"+Util.getERRO(e));
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+Util.getERRO(e));
                    }
                    try{
                    	   return cs.getInt(4);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(4):"+Util.getERRO(e));
                    	return null;
                    }
            }
        }).toString();
        log.info("sp_appl_tb_query_return_param2Value:"+param2Value);
        return param2Value;
    }
    
    public String sp_appl_tb_Tongbu(String APPNO1,int STATUS1,String ERR_CODE1,String ERR_DESC1){
    	final String APPNO=APPNO1;
    	log.info("APPNO:"+APPNO);
    	final int STATUS=STATUS1;
    	log.info(" STATUS:"+STATUS);
    	final String ERR_CODE=ERR_CODE1;
    	log.info("ERR_CODE:"+ERR_CODE);
    	final String ERR_DESC=ERR_DESC1;
    	log.info("ERR_DESC:"+ERR_DESC);
    	JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){            
                    String storedProc="{call sp_appl_tb_Tongbu(?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs=null;
                    try{
                    	cs=con.prepareCall(storedProc);
                   }catch(Exception e){
                	   log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                	   cs.setString(1, APPNO);// 设置输入参数的值
                   	   cs.setInt(2,STATUS);// 设置输入参数的值
                       cs.setString(3,ERR_CODE);// 设置输入参数的值
                       cs.setString(4,ERR_DESC);// 设置输入参数的值
                       cs.setInt(5,0);// 设置输入参数的值
                   }catch(Exception e){
                	   log.info("设置输入参数的值错误:"+Util.getERRO(e));
                   }
                   try{
                	   cs.registerOutParameter(5,Types.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){                	   
                	   log.info("注册输出参数的类型错误:"+Util.getERRO(e));
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+Util.getERRO(e));
                    }
                    try{
                    	   return cs.getInt(5);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(5):"+Util.getERRO(e));
                    	return null;
                    }
            }
        }).toString();
        log.info("sp_appl_tb_Tongbu_param2Value:"+param2Value);
        return param2Value;
    }
}
